﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RFIDStockManagement.Model.Public;

namespace RFIDStockManagement.DAL.Reports
{
    public class ReportStockCheck : DBReportBase
    {
        private int checkPId = 0;
        private int stockId = 0;

        private const string SELECT_INFO_SQL = @"
Select s2.FName as FitemName,s2.FNumber as FitemNumber,s4.FName as FStock,
		s1.FQty1/s3.FRate as FQty1,s1.FQty2/s3.FRate as FQty2,s3.FName as FUnit
From 
 (
	Select Isnull(t1.FitemId,t2.FitemID) as FitemId,
	IsNull(t1.FStockId,t2.FStockId) as FStockId,
	Isnull(t1.FQty,0) as FQty1,IsNull(t2.FQty,0) as FQty2
	From rfid_Inventory t1
	full join (Select Sum(tt2.FRate) as FQty,FitemId,FStockId from RFID_Stock_Check_Detail tt1
	Inner join rfid_Measure tt2 on tt1.FUnitId = tt2.Fid
	Where tt1.Fid = {0} 
	group by FitemID,FStockId) t2 on t1.FItemId = t2.FItemID And t1.FStockId = t2.FStockID
) s1
Inner join rfid_Item s2 on s2.Fid = s1.FItemID
Inner join rfid_Measure s3 on s2.FStockMeasureID = s3.FId
Inner join rfid_Stock s4 on s1.FStockId= s4.Fid  ";


        public ReportStockCheck()
        {
            this.ReportCaption = "盘点报告";
            this.ReportKey = "total_stock_check";
            InitColuns();
        }

        private void InitColuns()
        {
            ReportColumn newCol;
            ReportColumn newCol2;
            newCol = AddColumn("FitemName", "物料名称");
            newCol.Index = 1;

            newCol = AddColumn("FitemNumber", "物料代码");
            newCol.Index = 2;

            newCol = AddColumn("FStock", "仓库名称");
            newCol.Index = 3;

            newCol = AddColumn("FUnit", "单位名称");
            newCol.Index = 4;
            newCol.FormateString = "0.0000";

            newCol = AddColumn("FQty", "数量");
            newCol.Index = 5;


            newCol2 = new ReportColumn("FQty1", "系统库存量");
            newCol2.FormateString = "0.0000";
            newCol2.ColumnType = RFIDStockManagement.Common.Public.Enums.EnumDataTypes.TNumeric;
            newCol.SonColumns.Add(newCol2);

            newCol2 = new ReportColumn("FQty2", "盘点量");
            newCol2.FormateString = "0.0000";
            newCol2.ColumnType = RFIDStockManagement.Common.Public.Enums.EnumDataTypes.TNumeric;
            newCol.SonColumns.Add(newCol2);

        }

        public override System.Data.DataTable GetReportData()
        {
            string sql;
            string filter = GetFilterString();

            if (checkPId <= 0)
            {
                return new System.Data.DataTable();
            }
            else
            {
                sql = string.Format(SELECT_INFO_SQL, checkPId);
                sql += filter;

                return GetData(sql);
            }
        }

        private string GetFilterString()
        {
            string retString = "Where 1=1 ";
            string testStr = string.Empty;

            if (Filters == null)
            {
                checkPId = 0;
                stockId = 0;
                return string.Empty;
            }

            testStr = GetFilterData("stockcheckId");

            if (!int.TryParse(testStr, out checkPId))
            {
                checkPId = 0;
                return string.Empty;
            }

            testStr = GetFilterData("stockId");

            if (!int.TryParse(testStr, out stockId))
            {
                stockId = 0;

            }
            else
            {
                if (stockId != 0)
                {
                    retString += " And s4.FId = " + stockId.ToString();
                }
            }

            testStr = GetFilterData("FItemStart");

            if (!string.IsNullOrEmpty(testStr))
            {
                retString += " And s2.FName >='" + testStr + "' ";
            }

            testStr = GetFilterData("FItemEnd");

            if (!string.IsNullOrEmpty(testStr))
            {
                retString += " And s2.FName <='" + testStr + "' ";
            }

            return retString;

        }
    }
}
